﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.Objects.SqlClient;
using System.Data;

namespace BLL
{
    public partial class memberInfoManager
    {

        ZG_OAEntities ds = new ZG_OAEntities();
        //取最大的会员编号
        public string GetMaxMemberId()
        {
            return db.GetAll().OrderByDescending(p => p.id).ToList<memberInfo>()[0].memberId;

        }
        //根据会员编号取会员信息
        public memberInfo GetModel(string memberId)
        {
            return db.GetAll().FirstOrDefault(p => p.memberId == memberId);
        }
        //根据会员卡号取会员信息
        public memberInfo GetModelByMemberCardId(string memberCardId)
        {
            return db.GetAll().FirstOrDefault(p => p.memberCardId == memberCardId);
        }
        //取余额小于balance的会员信息
        public IQueryable<MemberList> GetAllMemberByBalance(int balance)
        {
            IQueryable<memberInfo> m = db.GetAll().Where(p => p.Balance <= balance);
            List<MemberList> list = new List<MemberList>();
            foreach (var p in m)
            {
                Model.MemberList ML = new MemberList();
                ML.id = p.id;
                ML.memberID = p.memberId;
                ML.membercardId = p.memberCardId;
                ML.memberName = p.memberName;
                ML.sex = p.sex == true ? "男" : "女";
                ML.mobile = p.mobile;
                ML.Tel = p.Tel;
                ML.Email = p.Email;
                ML.IDNumber = p.IDNumber;
                ML.birthDay = p.birthDay;
                ML.cardType = GetAllConsume(p.cardType.ToString());
                ML.Balance = p.Balance;
                ML.point = p.point;
                ML.status = p.status == 0 ? "正常" : p.status == 1 ? "挂失" : "停用";
                ML.createDate = p.createDate;
                ML.expiresDate = p.expiresDate;
                ML.consume = p.consume;
                ML.memo = p.memo;
                ML.pwd = p.pwd;
                ML.Marriage = p.Marriage;
                ML.Zip = p.Zip;
                ML.Address = p.Address;
                ML.areaID = GetAreasById(p.AreasID).ToString();
                ML.MemberType = p.MemberType;
                ML.shifouhuixing = p.shifouhuixing;
                ML.tuiguangren = p.tuiguangren;
                list.Add(ML);
            }
            IQueryable<MemberList> Member = list.AsQueryable();
            return Member;
        }
        public string GetAreasById(int? areaID)
        {
            var q = from a in ds.Areas
                    where a.id == areaID
                    select a.AreaName;
            return q.First().ToString();
        }

        public string GetAllConsume(string cardType)
        {

            int card;
            int.TryParse(cardType, out card);
            var q = from c in ds.consumeSolution
                    where c.id == card
                    select c.solutionName;


            return q.First().ToString();


        }
        //根据距离生日日期获取会员信息
        public IQueryable<MemberList> GetAllMemberByBirthday(int inputDay)
        {
            memberInfo member;
            List<MemberList> mlist = new List<MemberList>();
            var q = from m in ds.memberInfo
                    select new { m.id, m.birthDay, m.birthDay.Value.Year, m.birthDay.Value.Month, m.birthDay.Value.Day };
            foreach (var item in q)
            {
                DateTime birthday = new DateTime(DateTime.Now.Year, item.Month, item.Day);
                int BlODay = Convert.ToInt32(System.Data.Linq.SqlClient.SqlMethods.DateDiffDay(DateTime.Now.Date, birthday));
                if (BlODay <= inputDay && BlODay >= 0)
                {
                    member = GetByID(item.id);
                    MemberList ML = new MemberList();
                    ML.id = member.id;
                    ML.memberID = member.memberId;
                    ML.membercardId = member.memberCardId;
                    ML.memberName = member.memberName;
                    ML.sex = member.sex == true ? "男" : "女";
                    ML.mobile = member.mobile;
                    ML.Tel = member.Tel;
                    ML.Email = member.Email;
                    ML.IDNumber = member.IDNumber;
                    ML.birthDay = member.birthDay;
                    ML.cardType = GetAllConsume(member.cardType.ToString());
                    ML.Balance = member.Balance;
                    ML.point = member.point;
                    ML.status = member.status == 0 ? "正常" : member.status == 1 ? "挂失" : "停用";
                    ML.createDate = member.createDate;
                    ML.expiresDate = member.expiresDate;
                    ML.consume = member.consume;
                    ML.memo = member.memo;
                    ML.pwd = member.pwd;
                    ML.Marriage = member.Marriage;
                    ML.Zip = member.Zip;
                    ML.Address = member.Address;
                    ML.areaID = GetAreasById(member.AreasID).ToString();
                    ML.MemberType = member.MemberType;
                    ML.shifouhuixing = member.shifouhuixing;
                    ML.tuiguangren = member.tuiguangren;
                    mlist.Add(ML);
                }

            }
            return mlist.AsQueryable<MemberList>();
        }
      
        //根据条件获取会员信息
        public IQueryable<MemberList> GetMemberByConditions(string memberId, string memberName, string mobile, string select1, string select2, string select3, string tiaojian1, string tiaojian2, string tiaojian3)
        {
            List<MemberList> list = new List<MemberList>();
            var query = from obj in ds.memberInfo orderby obj.id descending select obj;
            if (!string.IsNullOrEmpty(memberId))
            {
                query = from obj in query where obj.memberId.Contains(memberId)  orderby obj.id descending select obj;
                          
            }
            if (!string.IsNullOrEmpty(memberName))
            {
                query = from obj in query where obj.memberName.Contains(memberName) orderby obj.id descending select obj;
            }
            if (!string.IsNullOrEmpty(mobile))
            {
                query = from obj in query where obj.mobile.Contains(mobile)  orderby obj.id descending select obj;
            }
            switch (select1)
            {
                case "Tel":
                    if(!string.IsNullOrEmpty(tiaojian1))
                    {
                        query = from obj in query where obj.Tel.Contains(tiaojian1) orderby obj.id descending select obj;
                       
                    }
                    break;
                case "sex":
                    if(!string.IsNullOrEmpty(tiaojian1))
                    {
                        if (tiaojian1 == "男")
                        {
                            query = from obj in query where obj.sex== true orderby obj.id descending select obj;
                        }
                        else
                        {
                            if (tiaojian1 == "女")
                            {
                                query = from obj in query where obj.sex == false orderby obj.id descending select obj;
                            }
                            else
                            {
                                break;

                            }
                        }
                    }
                    break;
                case "IDNumber":
                    if (!string.IsNullOrEmpty(tiaojian1))
                    {
                        query = from obj in query where obj.IDNumber.Contains(tiaojian1) orderby obj.id descending select obj;
                    }
                    break;
                case "Email":
                    if (!string.IsNullOrEmpty(tiaojian1))
                    {
                        query = from obj in query where obj.Email.Contains(tiaojian1) orderby obj.id descending select obj;
                    }
                    break;
                default:
                    break;
            }
            switch (select2)
            {
                case "Tel":
                    if (!string.IsNullOrEmpty(tiaojian2))
                    {
                        query = from obj in query where obj.Tel.Contains(tiaojian2) orderby obj.id descending select obj;

                    }
                    break;
                case "sex":
                    if (!string.IsNullOrEmpty(tiaojian2))
                    {
                        if (tiaojian2 == "男")
                        {
                            query = from obj in query where obj.sex == true orderby obj.id descending select obj;
                        }
                        else
                        {
                            if (tiaojian2 == "女")
                            {
                                query = from obj in query where obj.sex == false orderby obj.id descending select obj;
                            }
                            else
                            {
                                break;

                            }
                        }
                    }
                    break;
                case "IDNumber":
                    if (!string.IsNullOrEmpty(tiaojian2))
                    {
                        query = from obj in query where obj.IDNumber.Contains(tiaojian2) orderby obj.id descending select obj;
                    }
                    break;
                case "Email":
                    if (!string.IsNullOrEmpty(tiaojian2))
                    {
                        query = from obj in query where obj.Email.Contains(tiaojian2)  orderby obj.id descending select obj;
                    }
                    break;
                default:
                    break;
            }
            switch (select3)
            {
                case "Tel":
                    if (!string.IsNullOrEmpty(tiaojian3))
                    {
                        query = from obj in query where obj.Tel.Contains(tiaojian3) orderby obj.id descending select obj;

                    }
                    break;
                case "sex":
                    if (!string.IsNullOrEmpty(tiaojian3))
                    {
                        if (tiaojian3== "男")
                        {
                            query = from obj in query where obj.sex == true orderby obj.id descending select obj;
                        }
                        else
                        {
                            if (tiaojian3 == "女")
                            {
                                query = from obj in query where obj.sex == false orderby obj.id descending select obj;
                            }
                            else
                            {
                                break;

                            }
                        }
                    }
                    break;
                case "IDNumber":
                    if (!string.IsNullOrEmpty(tiaojian3))
                    {
                        query = from obj in query where obj.IDNumber.Contains(tiaojian3) orderby obj.id descending select obj;
                    }
                    break;
                case "Email":
                    if (!string.IsNullOrEmpty(tiaojian3))
                    {
                        query = from obj in query where obj.Email.Contains(tiaojian3) orderby obj.id descending select obj;
                    }
                    break;
                default:
                    break;
            }
            foreach (var p in query)
            {
                Model.MemberList ML = new MemberList();
                ML.id = p.id;
                ML.memberID = p.memberId;
                ML.membercardId = p.memberCardId;
                ML.memberName = p.memberName;
                ML.sex = p.sex == true ? "男" : "女";
                ML.mobile = p.mobile;
                ML.Tel = p.Tel;
                ML.Email = p.Email;
                ML.IDNumber = p.IDNumber;
                ML.birthDay = p.birthDay;
                ML.cardType = GetAllConsume(p.cardType.ToString());
                ML.Balance = p.Balance;
                ML.point = p.point;
                ML.status = p.status == 0 ? "正常" : p.status == 1 ? "挂失" : "停用";
                ML.createDate = p.createDate;
                ML.expiresDate = p.expiresDate;
                ML.consume = p.consume;
                ML.memo = p.memo;
                ML.pwd = p.pwd;
                ML.Marriage = p.Marriage;
                ML.Zip = p.Zip;
                ML.Address = p.Address;
                ML.areaID = GetAreasById(p.AreasID).ToString();
                ML.MemberType = p.MemberType;
                ML.shifouhuixing = p.shifouhuixing;
                ML.tuiguangren = p.tuiguangren;
                list.Add(ML);
            }
            IQueryable<MemberList> Member = list.AsQueryable();
            return Member;
        }

        //根据会员编号修改会员卡片状态
        public string UpdateStatus( string memberId, int select)
        {
            try {
                memberInfo m = db.GetAll().FirstOrDefault(p => p.memberId == memberId);
               
                switch (select)
                { 
                    case 0:
                        m.status = 0;
                        db.SaveChanges();
                       return "恢复正常成功";
                       
                    case 1:
                       m.status = 1;
                       db.SaveChanges();
                       return "挂失成功";
                      
                    case 2:
                       m.status = 2;
                       db.SaveChanges();
                       return "停用成功";
                       
                    default:
                       return "目标不明确，更新失败";
                       
                }
            }
            catch {
                return "出现错误，更新失败";
            }
         
        }
       
        //获取挂失或停用的会员信息
        public IQueryable<MemberList> getAllNo()
        {
            IQueryable<memberInfo> m = db.GetAll().Where(p => p.status != 0);
            List<MemberList> list = new List<MemberList>();
            foreach (var p in m)
            {
                Model.MemberList ML = new MemberList();
                ML.id = p.id;
                ML.memberID = p.memberId;
                ML.membercardId = p.memberCardId;
                ML.memberName = p.memberName;
                ML.sex = p.sex == true ? "男" : "女";
                ML.mobile = p.mobile;
                ML.Tel = p.Tel;
                ML.Email = p.Email;
                ML.IDNumber = p.IDNumber;
                ML.birthDay = p.birthDay;
                ML.cardType = GetAllConsume(p.cardType.ToString());
                ML.Balance = p.Balance;
                ML.point = p.point;
                ML.status = p.status == 0 ? "正常" : p.status == 1 ? "挂失" : "停用";
                ML.createDate = p.createDate;
                ML.expiresDate = p.expiresDate;
                ML.consume = p.consume;
                ML.memo = p.memo;
                ML.pwd = p.pwd;
                ML.Marriage = p.Marriage;
                ML.Zip = p.Zip;
                ML.Address = p.Address;
                ML.areaID = GetAreasById(p.AreasID).ToString();
                ML.MemberType = p.MemberType;
                ML.shifouhuixing = p.shifouhuixing;
                ML.tuiguangren = p.tuiguangren;
                list.Add(ML);
            }
            IQueryable<MemberList> Member = list.AsQueryable();
            return Member;
        }

        //会员充值
        public bool MemberRecharge(string memberId, decimal balance)
        {
            try
            {
                memberInfo m = db.GetAll().FirstOrDefault(p => p.memberId == memberId);
                m.Balance += balance;
                db.SaveChanges();
                return true;
            }
            catch {
                return false;
            }
           
        }

        //根据会员卡号返回会员Id
        public string getMemberIdByMemberCardId(string memberCardId) 
        {
            return db.GetAll().FirstOrDefault(p => p.memberCardId == memberCardId).memberId;
               
        }
        //根据会员Id删除会员
        public void Delmember(string memberId)
        {
            memberInfo m = db.GetAll().FirstOrDefault(p => p.memberId == memberId);
            db.Delete(m);
            db.SaveChanges();
        }
        //批量导出会员
        public IQueryable<MemberList> GetAllMember()
        {
            IQueryable<memberInfo> m = db.GetAll();
            List<MemberList> list = new List<MemberList>();
            foreach (var p in m)
            {
                Model.MemberList ML = new MemberList();
                ML.id = p.id;
                ML.memberID = p.memberId;
                ML.membercardId = p.memberCardId;
                ML.memberName = p.memberName;
                ML.sex = p.sex == true ? "男" : "女";
                ML.mobile = p.mobile;
                ML.Tel = p.Tel;
                ML.Email = p.Email;
                ML.IDNumber = p.IDNumber;
                ML.birthDay = p.birthDay;
                ML.cardType = GetAllConsume(p.cardType.ToString());
                ML.Balance = p.Balance;
                ML.point = p.point;
                ML.status = p.status == 0 ? "正常" : p.status == 1 ? "挂失" : "停用";
                ML.createDate = p.createDate;
                ML.expiresDate = p.expiresDate;
                ML.consume = p.consume;
                ML.memo = p.memo;
                ML.pwd = p.pwd;
                ML.Marriage = p.Marriage;
                ML.Zip = p.Zip;
                ML.Address = p.Address;
                ML.areaID = GetAreasById(p.AreasID).ToString();
                ML.MemberType = p.MemberType;
                ML.shifouhuixing = p.shifouhuixing;
                ML.tuiguangren = p.tuiguangren;
                list.Add(ML);
            }
            IQueryable<MemberList> Member = list.AsQueryable();
            return Member;
        }

        //根据余额和消费查找
        public DataTable getTopByBC(int Num,string cardType)
        {
           IQueryable<memberInfo> query;
            if (cardType == "0")
            {
                  query = (from obj in ds.memberInfo orderby obj.Balance + obj.consume descending select obj).Take(Num);
            }
            else
            {
                int[] cardId = new int[20];

                string[] cardtype = cardType.Split(',');
                for (int i = 0; i < cardtype.Count(); i++)
                {
                    cardId[i] = Convert.ToInt32(cardtype[i]);
                }
                var q = from obj in ds.memberInfo where cardId.Contains(obj.cardType) orderby obj.Balance + obj.consume descending select obj;
                  query = (from obj in q select obj).Take(Num);
            }
             
            DataTable tableS = new DataTable();
            tableS.Columns.Add("membercardId", typeof(string));
            tableS.Columns.Add("Balance", typeof(string));
            tableS.Columns.Add("memberName", typeof(string));
            tableS.Columns.Add("cardType", typeof(string));
            IEnumerable<memberInfo> database = query;
            foreach (memberInfo item in database)
            {
                DataRow dr = tableS.NewRow();
                dr["membercardId"] = item.memberCardId;
                dr["memberName"] = item.memberName;
                dr["cardType"] = GetAllConsume(item.cardType.ToString());
                dr["Balance"] = item.Balance + item.consume;
                tableS.Rows.Add(dr);
            }
            return tableS;
        }

        //根据卡片类型查找相应的卡片数量
        public int getNumByCardType(int cardType)
        {
            return db.GetAll().Where(p => p.cardType == cardType).Count();
        }

        //根据条件查找比例
        public IQueryable<memberInfo> getByInput( int sex,int memberLevel,int area)
        {
            var query = from obj in ds.memberInfo orderby obj.id  select obj;
            if (sex == 1)
            {
                 query = from obj in query where obj.sex == true orderby obj.id select obj;
            }
            else
            {
                if (sex == 2)
                {
                     query = from obj in query where obj.sex == false orderby obj.id select obj;
                }
            }
            query = from obj in query where obj.cardType == memberLevel orderby obj.id select obj;
            query = from obj in query where obj.AreasID == area orderby obj.id select obj;
            return query;

        }

        //年龄分析
        public DataTable getByAge()
        {
            DataTable dt = new DataTable();
            DataRow dr;
            DataColumn dc = new DataColumn("年龄",typeof(int));
            DataColumn dc1 = new DataColumn("人数", typeof(int));
            dt.Columns.Add(dc);
            dt.Columns.Add(dc1); 
            List<int> ageAll = new List<int>();
           
           
                var q = from m in ds.memberInfo
                        select new { m.id, m.birthDay, m.birthDay.Value.Year };
                foreach (var item in q)
                {

                    dr = dt.NewRow();
                    int age = DateTime.Now.Year - item.birthDay.Value.Year;
                    int num = db.GetAll().Where(p => DateTime.Now.Year - p.birthDay.Value.Year == age).Count();
                    if (!ageAll.Contains(age))
                    {
                        ageAll.Add(age);
                        dr["年龄"] = age;
                        dr["人数"] = num;
                        dt.Rows.Add(dr);
                    }

                }
          
            return dt;
        }

        //年龄分析
        public DataTable getByAge(string sex, string memberLevel, string consume, string inputNum)
        {
            DataTable dt = new DataTable();
            DataRow dr;
            DataColumn dc = new DataColumn("年龄", typeof(int));
            DataColumn dc1 = new DataColumn("人数", typeof(int));
            dt.Columns.Add(dc);
            dt.Columns.Add(dc1);
            List<int> ageAll = new List<int>();
            if (!string.IsNullOrEmpty(sex) && !string.IsNullOrEmpty(memberLevel))
            {
                var query = from obj in ds.memberInfo orderby obj.id select obj;
                if (Convert.ToInt32(sex) == 1)
                {
                    query = from obj in query where obj.sex == true orderby obj.id select obj;
                }
                else
                {
                    if (Convert.ToInt32(sex) == 2)
                    {
                        query = from obj in query where obj.sex == false orderby obj.id select obj;
                    }
                }
                int cardtype = Convert.ToInt32(memberLevel);
                query = from obj in query where obj.cardType == cardtype orderby obj.id select obj;
                if (!string.IsNullOrEmpty(consume) && !string.IsNullOrEmpty(inputNum))
                {
                    decimal money = Convert.ToDecimal(inputNum);
                    if (Convert.ToInt32(consume) == 0)
                    {
                        query = from obj in query where obj.consume > money orderby obj.id select obj;
                    }
                    else
                    {
                        if (Convert.ToInt32(consume) == 1)
                        {
                            query = from obj in query where obj.consume < money orderby obj.id select obj;
                        }
                    }
                }
                var q = from m in query
                        select new { m.id, m.birthDay, m.birthDay.Value.Year };
                foreach (var item in q)
                {

                    dr = dt.NewRow();
                    int age = DateTime.Now.Year - item.birthDay.Value.Year;
                    int num = db.GetAll().Where(p => DateTime.Now.Year - p.birthDay.Value.Year == age).Count();
                    if (!ageAll.Contains(age))
                    {
                        ageAll.Add(age);
                        dr["年龄"] = age;
                        dr["人数"] = num;
                        dt.Rows.Add(dr);
                    }

                }
            }
            return dt;
        }
    }
}
